package com.example.demo_server;


import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHelper";
    private static final String DATABASE_NAME = "db_student";// 数据库名
    private static final int DATABASE_VERSION = 1;// 数据库版本号
    private static final String TABLE_NAME_STUDENT = "student"; //学生表

    private static DBHelper mHelper = null;

    private DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //单例模式
    public static DBHelper getInstance(Context context) {
        if (mHelper == null) {
            mHelper = new DBHelper(context);
        }
        return mHelper;
    }

    //数据库创建时被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }

    //数据库升级
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * 初始化数据表
     *
     * @param db 数据库
     */
    private void initTable(SQLiteDatabase db) {
        // 创建用户表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT + " (" +
                "_id integer primary key autoincrement," +
                "name text," +
                "number integer," +
                "grade text," +
                "age integer)");
    }

    /**
     * 初始化数据库内部模拟数据(仅在第一次安装时执行)
     *
     * @param db 数据库
     */
    private void initData(SQLiteDatabase db) {

    }

    public void insertStudent(String name, int number, String grade, int age) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into " + TABLE_NAME_STUDENT +
                " (name, number, grade, age) " +
                "values" +
                "('" + name + "'," + number + ",'" + grade + "', " + age + ")");
    }

    public void insertStudent(Student student) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into " + TABLE_NAME_STUDENT +
                " (name, number, grade, age) " +
                "values" +
                "('" + student.getName() + "'," + student.getNumber() + ",'" + student.getGrade() + "', " + student.getAge() + ")");
    }

    //根据学号删除学生
    public void deleteStudentByNumber(int number) {
        if (number >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            int changedRowCount = db.delete(TABLE_NAME_STUDENT, "number = ?", new String[]{String.valueOf(number)});
            Log.d(TAG, "deleteStudentByNumber changedRowCount = " + changedRowCount);
        }
    }

    public void deleteStudentById(int id) {
        if (id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            int changedRowCount = db.delete(TABLE_NAME_STUDENT, "_id = ?", new String[]{String.valueOf(id)});
            Log.d(TAG, "deleteStudentById changedRowCount = " + changedRowCount);
        }
    }

    @SuppressLint("Range")
    public Student getStudentByNumber(int number) {
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_STUDENT,          // 表名
                null,                        // 要查询的列数据
                "number = ?",                // 条件语句
                new String[]{String.valueOf(number)},      // 条件语句的值
                null,                        // group by
                null,                        // having
                null                         // 排序语句
        );
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                Student student = new Student();
                student.setName(cursor.getString(cursor.getColumnIndex("name")));
                student.setNumber(cursor.getInt(cursor.getColumnIndex("number")));
                student.setGrade(cursor.getString(cursor.getColumnIndex("grade")));
                student.setAge(cursor.getInt(cursor.getColumnIndex("age")));
                return student;
            } while (cursor.moveToNext());
        }
        return null;
    }

    @SuppressLint("Range")
    public List<Student> getStudentList() {
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_STUDENT,          // 表名
                null,                        // 要查询的列数据
                null,                        // 条件语句
                null,                        // 条件语句的值
                null,                        // group by
                null,                        // having
                null                         // 排序语句
        );
        if (cursor.moveToFirst()) {
            List<Student> students = new ArrayList<>();
            do {
                Student student = new Student();
                student.setName(cursor.getString(cursor.getColumnIndex("name")));
                student.setNumber(cursor.getInt(cursor.getColumnIndex("number")));
                student.setGrade(cursor.getString(cursor.getColumnIndex("grade")));
                student.setAge(cursor.getInt(cursor.getColumnIndex("age")));
                students.add(student);
            } while (cursor.moveToNext());
            return students;
        }
        return null;
    }
}